package com.example.dblibrary.database.daos;


import android.arch.persistence.room.Dao;
import android.arch.persistence.room.Query;

import com.example.dblibrary.database.AppDatabase;
import com.example.dblibrary.database.entities.Song;

import java.util.List;

import io.reactivex.Flowable;

@Dao
public interface SongDao {

    /**
     * 获取数据总量
     *
     * @return
     */
    @Query("SELECT COUNT(*) FROM " + AppDatabase.table_song)
    Flowable<Long> count();


    /**
     * 分页查询(种类查询)
     *
     * @param startIndex
     * @param offset
     * @return
     */
    @Query("SELECT * from " + AppDatabase.table_song + " WHERE length(SONGS_TYPE)=:type AND SONGS_STATUS >:status ORDER BY SONGS_TEMP DESC LIMIT :startIndex ,:offset")
    Flowable<List<Song>> loadSongsByPageOffset(int startIndex, int offset, int type, int status);


    /**
     * 分页查询(种类-简写)
     *
     * @param startIndex
     * @param offset
     * @return
     */
    @Query("SELECT * from " + AppDatabase.table_song + " WHERE length(SONGS_TYPE)=:type AND SONGS_STATUS >:status AND SONGS_FIRST_SPELL LIKE '%' || :spell || '%' ORDER BY SONGS_TEMP DESC LIMIT :startIndex ,:offset")
    Flowable<List<Song>> loadSongsInCgSpByPageOffset(int startIndex, int offset, int type, int status, String spell);

    /**
     * 分页查询(种类-手写)
     *
     * @param startIndex
     * @param offset
     * @return
     */
    @Query("SELECT * from " + AppDatabase.table_song + " WHERE length(SONGS_TYPE)=:type AND SONGS_STATUS >:status  AND SONGS_NAME LIKE '%' || :name || '%' ORDER BY SONGS_TEMP DESC LIMIT :startIndex ,:offset")
    Flowable<List<Song>> loadSongsInCgHdByPageOffset(int startIndex, int offset, int type, int status, String name);


    /**
     * 分页查询(种类查询)
     *
     * @param startIndex
     * @param offset
     * @return
     */
    @Query("SELECT * from " + AppDatabase.table_song + " WHERE  SONGS_STATUS >:status ORDER BY SONGS_TEMP DESC LIMIT :startIndex ,:offset")
    Flowable<List<Song>> loadSongsByPageOffset2(int startIndex, int offset,  int status);


    /**
     * 分页查询(种类-简写)
     *
     * @param startIndex
     * @param offset
     * @return
     */
    @Query("SELECT * from " + AppDatabase.table_song + " WHERE SONGS_STATUS >:status AND SONGS_FIRST_SPELL LIKE '%' || :spell || '%' ORDER BY SONGS_TEMP DESC LIMIT :startIndex ,:offset")
    Flowable<List<Song>> loadSongsInCgSpByPageOffset2(int startIndex, int offset, int status, String spell);

    /**
     * 分页查询(种类-手写)
     *
     * @param startIndex
     * @param offset
     * @return
     */
    @Query("SELECT * from " + AppDatabase.table_song + " WHERE SONGS_STATUS >:status  AND SONGS_NAME LIKE '%' || :name || '%' ORDER BY SONGS_TEMP DESC LIMIT :startIndex ,:offset")
    Flowable<List<Song>> loadSongsInCgHdByPageOffset2(int startIndex, int offset, int status, String name);



    /**
     * 批量查询
     *
     * @param startIndex
     * @param offset
     * @return
     */
    @Query("SELECT * from " + AppDatabase.table_song + " WHERE  SONGS_STATUS >:status  AND SONGS_NUM IN (:num) ORDER BY SONGS_TEMP DESC LIMIT :startIndex ,:offset")
    Flowable<List<Song>> loadSongsInBatchByPageOffset(int startIndex, int offset, int status, List<Integer> num);

}

